
if exists(select name from sys.objects where type = 'V' and name = 'vSkillsCourseStudentOnlyAnswer')
BEGIN
	DROP VIEW [vSkillsCourseStudentOnlyAnswer]
END
GO

CREATE VIEW [vSkillsCourseStudentOnlyAnswer]
AS
--AMG 14/01/2024
--Only the Avg of the Course Student answers to the questions are returned - tabular
	SELECT 
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.DefaultDescription AS SkillsPeriodDescription,
		SkillsPeriod.ID AS SkillsPeriodID,
		SkillsCourseStudentAnswer.OGP_StudentID,
		SkillsCourseStudentAnswer.CourseID,
		Course.CourseCode,
		CASE WHEN Count(SkillsAnswer.[Value]) IS NULL THEN NULL ELSE
			CAST(CAST((Sum(SkillsAnswer.[Value])) AS decimal(19,2)) / (Count(SkillsAnswer.[Value])) AS decimal(19,2)) 
		END AS AvgAnswerValue,
		Count(SkillsAnswer.[Value]) AS CountofAnswers
	FROM 
		SkillsCourseStudentAnswer
		INNER JOIN SkillsAnswer ON SkillsCourseStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsPeriod ON SkillsCourseStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
		INNER JOIN Course ON SkillsCourseStudentAnswer.CourseID = Course.ID
	GROUP BY
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.ID,
		SkillsPeriod.DefaultDescription,
		SkillsCourseStudentAnswer.OGP_StudentID,
		SkillsCourseStudentAnswer.CourseID,
		Course.CourseCode
GO

if exists(select name from sys.objects where type = 'V' and name = 'vSkillsCourseStudentOnlyAnswer_AvgPivot')
BEGIN
	DROP VIEW [vSkillsCourseStudentOnlyAnswer_AvgPivot]
END
GO

CREATE VIEW [vSkillsCourseStudentOnlyAnswer_AvgPivot]
AS
--AMG 14/01/2024
--Only the Avg of the Course Student answers to the questions are returned - but Pivoted
SELECT 
	AcademicYearID, OGP_StudentID,  CourseID, CourseCode,
	[Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, CourseID, CourseCode, AvgAnswerValue
		FROM vSkillsCourseStudentOnlyAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10])
	) AS PivotTable

GO

if exists(select name from sys.objects where type = 'V' and name = 'vSkillsCourseStudentOnlyAnswer_CountPivot')
BEGIN
	DROP VIEW [vSkillsCourseStudentOnlyAnswer_CountPivot]
END
GO

CREATE VIEW [vSkillsCourseStudentOnlyAnswer_CountPivot]
AS
--AMG 14/01/2024
--Only the Count of the Answers to the Course questions are returned - but Pivoted
SELECT 
	AcademicYearID, OGP_StudentID,  CourseID, CourseCode,
	[Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, CourseID, CourseCode, CountofAnswers
		FROM vSkillsCourseStudentOnlyAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(CountofAnswers)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10])
	) AS PivotTable

GO


if exists(select name from sys.objects where type = 'V' and name = 'vSkillsOverallStudentAnswer')
BEGIN
	DROP VIEW [vSkillsOverallStudentAnswer]
END
GO


CREATE VIEW [vSkillsOverallStudentAnswer]
AS
--AMG 28/12/2023
--A set of course answers consists of the answers to the course questions, plus the answer to the student questions
	SELECT 
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.DefaultDescription AS SkillsPeriodDescription,
		SkillsPeriod.ID AS SkillsPeriodID,
		SkillsCourseStudentAnswer.OGP_StudentID,
		SkillsCourseStudentAnswer.CourseID,
		CASE WHEN Count(SkillsAnswer.[Value]) + t.CountAnswerValue IS NULL THEN NULL ELSE
			CAST(CAST((Sum(SkillsAnswer.[Value]) + t.SumAnswerValue) AS decimal(19,2)) / (Count(SkillsAnswer.[Value]) + t.CountAnswerValue) AS decimal(19,2)) 
		END AS AvgAnswerValue,
		Count(SkillsAnswer.[Value]) + t.CountAnswerValue AS CountOfAnswers
	FROM 
		SkillsCourseStudentAnswer
		INNER JOIN SkillsAnswer ON SkillsCourseStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsPeriod ON SkillsCourseStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
		INNER JOIN 
					(
					SELECT 
						SkillsStudentAnswer.OGP_StudentID,
						SkillsPeriod.DefaultDescription,
						Sum(SkillsAnswer.[Value]) AS SumAnswerValue,
						Count(SkillsAnswer.[Value]) AS CountAnswerValue
					FROM 
						SkillsStudentAnswer
						INNER JOIN SkillsAnswer ON SkillsStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
						INNER JOIN SkillsPeriod ON SkillsStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
					GROUP BY
						SkillsStudentAnswer.OGP_StudentID,
						SkillsPeriod.DefaultDescription
					) t
					ON SkillsCourseStudentAnswer.OGP_StudentID = t.OGP_StudentID
					AND SkillsPeriod.DefaultDescription = t.DefaultDescription
	GROUP BY
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.ID,
		SkillsPeriod.DefaultDescription,
		SkillsCourseStudentAnswer.OGP_StudentID,
		SkillsCourseStudentAnswer.CourseID,
		t.SumAnswerValue,
		t.CountAnswerValue

GO

if exists(select name from sys.objects where type = 'V' and name = 'vSkillsOverallStudentAnswer_AvgPivot')
BEGIN
	DROP VIEW [vSkillsOverallStudentAnswer_AvgPivot]
END
GO

CREATE VIEW [vSkillsOverallStudentAnswer_AvgPivot]
AS
--AMG 14/01/2023
--The Overall Avg consists of the answers to the course questions, plus the answer to the student questions - Pivoted

SELECT 
	AcademicYearID, OGP_StudentID,
	[Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, AvgAnswerValue
		FROM vSkillsOverallStudentAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10])
	) AS PivotTable

GO

if exists(select name from sys.objects where type = 'V' and name = 'vSkillsOverallStudentAnswer_CountPivot')
BEGIN
	DROP VIEW [vSkillsOverallStudentAnswer_CountPivot]
END
GO

CREATE VIEW [vSkillsOverallStudentAnswer_CountPivot]
AS
--AMG 14/01/2023
--The Count of Answers to the the course questions, plus the answer to the student questions - Pivoted

SELECT 
	AcademicYearID, OGP_StudentID,
	[Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, CountOfAnswers
		FROM vSkillsOverallStudentAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(CountOfAnswers)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10])
	) AS PivotTable

GO

if exists(select name from sys.objects where type = 'V' and name = 'vSkillsStudentOnlyAnswer')
BEGIN
	DROP VIEW [vSkillsStudentOnlyAnswer]
END
GO

CREATE VIEW [vSkillsStudentOnlyAnswer]
AS
--AMG 14/01/2024
--Only the Avg of the Student answers to the questions are returned - tabular
	SELECT 
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.DefaultDescription AS SkillsPeriodDescription,
		SkillsPeriod.ID AS SkillsPeriodID,
		SkillsStudentAnswer.OGP_StudentID,
		CASE WHEN Count(SkillsAnswer.[Value]) IS NULL THEN NULL ELSE
			CAST(CAST((Sum(SkillsAnswer.[Value])) AS decimal(19,2)) / (Count(SkillsAnswer.[Value])) AS decimal(19,2)) 
		END AS AvgAnswerValue,
		Count(SkillsAnswer.[Value]) AS CountofAnswers
	FROM 
		SkillsStudentAnswer
		INNER JOIN SkillsAnswer ON SkillsStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsPeriod ON SkillsStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
	GROUP BY
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.ID,
		SkillsPeriod.DefaultDescription,
		SkillsStudentAnswer.OGP_StudentID

GO

if exists(select name from sys.objects where type = 'V' and name = 'vSkillsStudentOnlyAnswer_AvgPivot')
BEGIN
	DROP VIEW [vSkillsStudentOnlyAnswer_AvgPivot]
END
GO


CREATE VIEW [vSkillsStudentOnlyAnswer_AvgPivot]
AS
--AMG 14/01/2023
--The Avg of the Student answers to the student questions - Pivoted
SELECT 
	AcademicYearID, OGP_StudentID,
	[Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID, AvgAnswerValue
		FROM vSkillsStudentOnlyAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(AvgAnswerValue)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10])
	) AS PivotTable
	
GO


if exists(select name from sys.objects where type = 'V' and name = 'vSkillsStudentOnlyAnswer_CountPivot')
BEGIN
	DROP VIEW [vSkillsStudentOnlyAnswer_CountPivot]
END
GO

CREATE VIEW [vSkillsStudentOnlyAnswer_CountPivot]
AS
--AMG 14/01/2023
--The Count of the Student answers to the student questions - Pivoted
SELECT 
	AcademicYearID, OGP_StudentID,
	[Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10]

FROM  
	(
		SELECT AcademicYearID, SkillsPeriodDescription, OGP_StudentID,  CountofAnswers
		FROM vSkillsStudentOnlyAnswer
	) AS sourcetable
	PIVOT  
	(  
	  MAX(CountofAnswers)--There should only be one value, but we need MAX, MIN, SUM, COUNT or something.
	  FOR SkillsPeriodDescription IN ([Period1], [Period2], [Period3], [Period4], [Period5], [Period6], [Period7], [Period8], [Period9], [Period10])
	) AS PivotTable

GO


ALTER VIEW [dbo].[vSkillsOverallStudentAnswer]
AS
--AMG 28/12/2023
--A set of course answers consists of the answers to the course questions, plus the answer to the student questions
--AMG 08/06/2024
--Removed the CourseID Grouping - we don't want an overall by Course, we want overall by student.
	SELECT 
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.DefaultDescription AS SkillsPeriodDescription,
		SkillsPeriod.ID AS SkillsPeriodID,
		SkillsCourseStudentAnswer.OGP_StudentID,
		--SkillsCourseStudentAnswer.CourseID,
		CASE WHEN Count(SkillsAnswer.[Value]) + t.CountAnswerValue IS NULL THEN NULL ELSE
			CAST(CAST((Sum(SkillsAnswer.[Value]) + t.SumAnswerValue) AS decimal(19,2)) / (Count(SkillsAnswer.[Value]) + t.CountAnswerValue) AS decimal(19,2)) 
		END AS AvgAnswerValue,
		Count(SkillsAnswer.[Value]) + t.CountAnswerValue AS CountOfAnswers
	FROM 
		SkillsCourseStudentAnswer
		INNER JOIN SkillsAnswer ON SkillsCourseStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsPeriod ON SkillsCourseStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
		INNER JOIN 
					(
					SELECT 
						SkillsStudentAnswer.OGP_StudentID,
						SkillsPeriod.DefaultDescription,
						Sum(SkillsAnswer.[Value]) AS SumAnswerValue,
						Count(SkillsAnswer.[Value]) AS CountAnswerValue
					FROM 
						SkillsStudentAnswer
						INNER JOIN SkillsAnswer ON SkillsStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
						INNER JOIN SkillsPeriod ON SkillsStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
					GROUP BY
						SkillsStudentAnswer.OGP_StudentID,
						SkillsPeriod.DefaultDescription
					) t
					ON SkillsCourseStudentAnswer.OGP_StudentID = t.OGP_StudentID
					AND SkillsPeriod.DefaultDescription = t.DefaultDescription
	GROUP BY
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.ID,
		SkillsPeriod.DefaultDescription,
		SkillsCourseStudentAnswer.OGP_StudentID,
		--SkillsCourseStudentAnswer.CourseID,
		t.SumAnswerValue,
		t.CountAnswerValue

GO


ALTER VIEW [vSkillsOverallStudentAnswer]
AS
--AMG 28/12/2023
--A set of course answers consists of the answers to the course questions, plus the answer to the student questions
--AMG 08/06/2024
--Removed the CourseID Grouping - we don't want an overall by Course, we want overall by student.
--AMG 10/06/2024
--The overall was only being calculated if we had both course and cross-college answers, that has now been fixed by the UNION ALL

SELECT 
		t.AcademicYearID,
		t.SkillsPeriodDescription AS SkillsPeriodDescription,
		t.SkillsPeriodID AS SkillsPeriodID,
		t.OGP_StudentID,
		CASE WHEN Count(t.[Value])  IS NULL THEN NULL ELSE
			CAST(CAST((Sum(t.[Value])) AS decimal(19,2)) / (Count(t.[Value])) AS decimal(19,2)) 
		END AS AvgAnswerValue,
		Count(t.[Value]) AS CountOfAnswers
FROM
	(
	SELECT 
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.DefaultDescription AS SkillsPeriodDescription,
		SkillsPeriod.ID AS SkillsPeriodID,
		SkillsCourseStudentAnswer.OGP_StudentID,
		SkillsAnswer.[Value]

	FROM 
		SkillsCourseStudentAnswer
		INNER JOIN SkillsAnswer ON SkillsCourseStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsPeriod ON SkillsCourseStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 

	UNION ALL

	SELECT 
		SkillsPeriod.AcademicYearID,
		SkillsPeriod.DefaultDescription AS SkillsPeriodDescription,
		SkillsPeriod.ID AS SkillsPeriodID,
		SkillsStudentAnswer.OGP_StudentID,
		SkillsAnswer.[Value]
	FROM 
		SkillsStudentAnswer
		INNER JOIN SkillsAnswer ON SkillsStudentAnswer.SkillsAnswerID = SkillsAnswer.ID
		INNER JOIN SkillsPeriod ON SkillsStudentAnswer.SkillsPeriodID = SkillsPeriod.ID 
	) t

GROUP BY 
		AcademicYearID,
		SkillsPeriodDescription,
		SkillsPeriodID,
		OGP_StudentID


GO
